#!/usr/bin/env node

const Database = require('better-sqlite3');
const db = new Database('./data/delivery-management.db');

console.log('🔍 分析对象类和实例表数量差异...\n');

try {
  console.log('1. 分析devices表中的所有对象类...');
  
  // 获取devices表中的所有对象类
  const devicesClasses = db.prepare(`
    SELECT DISTINCT
      d.classification_name,
      d.specialty,
      COUNT(d.device_id) as device_count,
      GROUP_CONCAT(DISTINCT d.device_id) as device_ids
    FROM devices d
    WHERE d.classification_name IS NOT NULL 
      AND d.classification_name != ''
    GROUP BY d.classification_name, d.specialty
    ORDER BY d.specialty, d.classification_name
  `).all();

  console.log(`devices表中的对象类总数: ${devicesClasses.length}`);

  console.log('\n2. 分析device_type_mappings表中的对象类映射...');
  
  // 获取映射表中的对象类
  const mappingClasses = db.prepare(`
    SELECT DISTINCT
      dtm.classification_name,
      dtm.specialty,
      dtm.english_name,
      COUNT(*) as mapping_count,
      GROUP_CONCAT(DISTINCT dtm.classification_code) as classification_codes
    FROM device_type_mappings dtm
    WHERE dtm.classification_name IS NOT NULL 
      AND dtm.classification_name != ''
    GROUP BY dtm.classification_name, dtm.specialty, dtm.english_name
    ORDER BY dtm.specialty, dtm.classification_name
  `).all();

  console.log(`映射表中的对象类映射总数: ${mappingClasses.length}`);

  console.log('\n3. 找出只在devices表中存在但没有映射的对象类...');
  
  // 创建映射表中对象类的集合
  const mappedClasses = new Set();
  mappingClasses.forEach(cls => {
    mappedClasses.add(`${cls.classification_name}|${cls.specialty}`);
  });

  const unmappedClasses = [];
  devicesClasses.forEach(cls => {
    const key = `${cls.classification_name}|${cls.specialty}`;
    if (!mappedClasses.has(key)) {
      unmappedClasses.push(cls);
    }
  });

  console.log(`没有映射的对象类数量: ${unmappedClasses.length}`);
  
  if (unmappedClasses.length > 0) {
    console.log('\n❌ 没有映射的对象类:');
    unmappedClasses.forEach(cls => {
      console.log(`  ${cls.classification_name} (${cls.specialty})`);
      console.log(`    设备数量: ${cls.device_count}`);
      console.log(`    设备ID: ${cls.device_ids}`);
      console.log('');
    });
  }

  console.log('\n4. 找出有映射但没有english_name的对象类...');
  
  const noEnglishNameClasses = mappingClasses.filter(cls => 
    !cls.english_name || cls.english_name.trim() === ''
  );

  console.log(`没有english_name的映射数量: ${noEnglishNameClasses.length}`);
  
  if (noEnglishNameClasses.length > 0) {
    console.log('\n⚠️  没有english_name的映射:');
    noEnglishNameClasses.forEach(cls => {
      console.log(`  ${cls.classification_name} (${cls.specialty})`);
      console.log(`    分类代码: ${cls.classification_codes}`);
      console.log('');
    });
  }

  console.log('\n5. 分析可以生成标准实例表的对象类...');
  
  const specialtyPrefixes = {
    '配管专业': 'piping',
    '电气专业': 'electrical',
    '暖通专业': 'hvac',
    '通信专业': 'communication',
    '仪表专业': 'instrumentation'
  };

  const validMappings = mappingClasses.filter(cls => 
    cls.english_name && 
    cls.english_name.trim() !== '' && 
    specialtyPrefixes[cls.specialty]
  );

  console.log(`可以生成标准实例表的映射数量: ${validMappings.length}`);

  // 去重 - 按classification_name + specialty分组
  const uniqueValidClasses = new Map();
  validMappings.forEach(cls => {
    const key = `${cls.classification_name}|${cls.specialty}`;
    if (!uniqueValidClasses.has(key)) {
      uniqueValidClasses.set(key, cls);
    }
  });

  console.log(`去重后的可生成表的对象类数量: ${uniqueValidClasses.size}`);

  console.log('\n6. 分析差异原因...');
  
  const totalDevicesClasses = devicesClasses.length;  // 87
  const validStandardTables = uniqueValidClasses.size; // 85
  const difference = totalDevicesClasses - validStandardTables;

  console.log(`\n📊 数量对比:`);
  console.log(`- devices表中对象类: ${totalDevicesClasses}`);
  console.log(`- 标准实例表数量: ${validStandardTables}`);
  console.log(`- 差异: ${difference}`);

  console.log(`\n🔍 差异构成:`);
  console.log(`- 没有映射的对象类: ${unmappedClasses.length}`);
  console.log(`- 没有english_name的映射: ${noEnglishNameClasses.length}`);

  // 检查是否有重复的english_name导致表合并
  console.log('\n7. 检查english_name重复情况...');
  
  const englishNameGroups = {};
  validMappings.forEach(cls => {
    const key = `${cls.specialty}|${cls.english_name}`;
    if (!englishNameGroups[key]) {
      englishNameGroups[key] = [];
    }
    englishNameGroups[key].push(cls);
  });

  const duplicateEnglishNames = Object.entries(englishNameGroups).filter(([key, classes]) => 
    classes.length > 1
  );

  if (duplicateEnglishNames.length > 0) {
    console.log(`\n🔄 重复的english_name (${duplicateEnglishNames.length}组):`);
    duplicateEnglishNames.forEach(([key, classes]) => {
      const [specialty, englishName] = key.split('|');
      console.log(`\n  ${englishName} (${specialty}):`);
      classes.forEach(cls => {
        console.log(`    - ${cls.classification_name}`);
      });
    });
  }

  console.log('\n✅ 分析完成!');
  console.log(`\n💡 总结:`);
  console.log(`87个对象类中，有${unmappedClasses.length}个没有映射，${noEnglishNameClasses.length}个没有英文名`);
  console.log(`因此只能为${validStandardTables}个对象类生成标准实例表`);

} catch (error) {
  console.error('❌ 分析失败:', error.message);
  console.error(error.stack);
} finally {
  db.close();
}